{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exploring Favourite Recipes\n",
    "\n",
    "Recipe websites allow you to bookmark certain recipes as \"favourites\". A student named Jeremy Cohen pulled together a sample of such data for an [excellent machine learning project](http://www.jeremymcohen.net/posts/taste/) and we'll use his dataset to demo how to do some unsupervised machine learning with MLDB."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The notebook cells below use `pymldb`'s `Connection` class to make [REST API](../../../../doc/#builtin/WorkingWithRest.md.html) calls. You can check out the [Using `pymldb` Tutorial](../../../../doc/nblink.html#_tutorials/Using pymldb Tutorial) for more details."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from pymldb import Connection\n",
    "mldb = Connection(\"http://localhost/\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The sequence of procedures below is based on the one explained in the [Mapping Reddit](../../../../doc/nblink.html#_demos/Mapping Reddit) demo notebook.\n",
    "\n",
    "First we import the raw data and make a sparse matrix out of it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n",
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put('/v1/procedures/import_rcp', {\n",
    "    \"type\": \"import.text\",\n",
    "    \"params\": {\n",
    "        \"headers\": [\"user_id\", \"recipe_id\"],\n",
    "        \"dataFileUrl\": \"http://public.mldb.ai/favorites.csv.gz\",\n",
    "        \"outputDataset\": \"rcp_raw\",\n",
    "        \"runOnCreation\": True\n",
    "    }\n",
    "})\n",
    "\n",
    "print mldb.post('/v1/procedures', {\n",
    "    \"id\": \"rcp_import\",\n",
    "    \"type\": \"transform\",\n",
    "    \"params\": {\n",
    "        \"inputData\": \"select pivot(recipe_id, 1) as * named user_id from rcp_raw group by user_id\",\n",
    "        \"outputDataset\": \"recipes\",\n",
    "        \"runOnCreation\": True\n",
    "    }\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We then train an SVD decomposition and do K-Means clustering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n",
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.post('/v1/procedures', {\n",
    "    \"id\": \"rcp_svd\",\n",
    "    \"type\" : \"svd.train\",\n",
    "    \"params\" : {\n",
    "        \"trainingData\": \"select * from recipes\",\n",
    "        \"columnOutputDataset\" : \"rcp_svd_embedding_raw\",\n",
    "        \"runOnCreation\": True\n",
    "    }\n",
    "})\n",
    "\n",
    "num_centroids = 16\n",
    "\n",
    "print mldb.post('/v1/procedures', {\n",
    "    \"id\" : \"rcp_kmeans\",\n",
    "    \"type\" : \"kmeans.train\",\n",
    "    \"params\" : {\n",
    "        \"trainingData\" : \"select * from rcp_svd_embedding_raw\",\n",
    "        \"outputDataset\" : \"rcp_kmeans_clusters\",\n",
    "        \"centroidsDataset\" : \"rcp_kmeans_centroids\",\n",
    "        \"numClusters\" : num_centroids,\n",
    "        \"runOnCreation\": True\n",
    "    }\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we import the actual recipe names, clean them up a bit, and get a version of our SVD embedding with the recipe names as column names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n",
      "<Response [201]>\n",
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put('/v1/procedures/import_rcp_names_raw', {\n",
    "    'type': 'import.text',\n",
    "    'params': {\n",
    "        'dataFileUrl': 'http://public.mldb.ai/recipes.csv.gz',\n",
    "        'outputDataset': \"rcp_names_raw\",\n",
    "        'delimiter':'',\n",
    "        'quoteChar':'',\n",
    "        'runOnCreation': True\n",
    "    }\n",
    "})\n",
    "\n",
    "print mldb.put('/v1/procedures/rcp_names_import', {\n",
    "    'type': 'transform',\n",
    "    'params': {\n",
    "        'inputData': '''\n",
    "            select jseval(\n",
    "               'return s.substr(s.indexOf(\",\") + 1)\n",
    "                .replace(/&#34;/g, \"\")\n",
    "                .replace(/&#174;/g, \"\");',\n",
    "            's', lineText) as name\n",
    "            named implicit_cast(rowName()) - 1\n",
    "            from rcp_names_raw\n",
    "        ''',\n",
    "        'outputDataset': 'rcp_names',\n",
    "        'runOnCreation': True\n",
    "    }\n",
    "})\n",
    "\n",
    "print mldb.put('/v1/procedures/rcp_clean_svd', {\n",
    "    'type': 'transform',\n",
    "    'params': {\n",
    "        'inputData': \"\"\"\n",
    "            select rcp_svd_embedding_raw.* as *\n",
    "            named rcp_names.rowName()+'-'+rcp_names.name \n",
    "            from rcp_svd_embedding_raw\n",
    "                join rcp_names on (rcp_names.rowName() = rcp_svd_embedding_raw.rowPathElement(0))\n",
    "        \"\"\",\n",
    "        'outputDataset': {'id': 'rcp_svd_embedding',\n",
    "                          'type': 'embedding',\n",
    "                          'params': {'metric': 'cosine'}},\n",
    "        'runOnCreation': True\n",
    "    }\n",
    "})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With all that pre-processing done, let's look at the names of the 3 closest recipes to each cluster centroid to try to get a sense of what kind of clusters we got."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>African Curry</td>\n",
       "      <td>Chap Chee Noodles</td>\n",
       "      <td>Sesame Crusted Mahi Mahi with Soy Shiso Ginger...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Traditional Christmas Cheese Ball</td>\n",
       "      <td>Old School Mac n' Cheese</td>\n",
       "      <td>Superb Sauteed Mushrooms</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Cheese Grits</td>\n",
       "      <td>Teriyaki Mushrooms</td>\n",
       "      <td>Country Scalloped Potatoes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cranberry Bars</td>\n",
       "      <td>Cranberry Upside</td>\n",
       "      <td>Autumn Harvest Cookies</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Fabulous French Loaves</td>\n",
       "      <td>Tasty Buns</td>\n",
       "      <td>Mama D's Italian Bread</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Blueberry Cream Cheese Pound Cake I</td>\n",
       "      <td>Blueberry Cream Cheese Pound Cake II</td>\n",
       "      <td>Hawaiian Banana Nut Bread</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Pro Ganache</td>\n",
       "      <td>Coffee Butter Frosting</td>\n",
       "      <td>Strawberry Cake and Frosting I</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Angel's Chunky Chicken Salad</td>\n",
       "      <td>Baked Ham</td>\n",
       "      <td>Incredible Potato Casserole</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Sausage Flowers</td>\n",
       "      <td>Fried Cabbage with Bacon, Onion, and Garlic</td>\n",
       "      <td>Sesame Noodle Salad</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Coffee Shake</td>\n",
       "      <td>Herbie's Home Fries</td>\n",
       "      <td>Chocolate Wontons</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Poblano Chile Enchiladas a la Gringa</td>\n",
       "      <td>Carnitas Filling</td>\n",
       "      <td>Daddy's 'If They'da had This at the Alamo we w...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Vegan Red Lentil Soup</td>\n",
       "      <td>Spinach, Red Lentil, and Bean Curry</td>\n",
       "      <td>Lentils And Spinach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Slow Cooker Thanksgiving Turkey</td>\n",
       "      <td>Slow Cooker BBQ Pork Chops</td>\n",
       "      <td>Slow Cooker Pork Chops</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Colette's Smoked Sausage Fritatta</td>\n",
       "      <td>Filet Mignons With Pepper Cream Sauce</td>\n",
       "      <td>Hidden Cheeseburger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Spicy Flank Steak</td>\n",
       "      <td>Blissful Rosemary Chicken</td>\n",
       "      <td>Chicken Melt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Chicago Dip</td>\n",
       "      <td>Spaghetti Salad I</td>\n",
       "      <td>Cheesy  Potato Casserole</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             0  \\\n",
       "_rowName                                         \n",
       "0                                African Curry   \n",
       "1            Traditional Christmas Cheese Ball   \n",
       "2                                 Cheese Grits   \n",
       "3                               Cranberry Bars   \n",
       "4                       Fabulous French Loaves   \n",
       "5          Blueberry Cream Cheese Pound Cake I   \n",
       "6                                  Pro Ganache   \n",
       "7                 Angel's Chunky Chicken Salad   \n",
       "8                              Sausage Flowers   \n",
       "9                                 Coffee Shake   \n",
       "10        Poblano Chile Enchiladas a la Gringa   \n",
       "11                       Vegan Red Lentil Soup   \n",
       "12             Slow Cooker Thanksgiving Turkey   \n",
       "13           Colette's Smoked Sausage Fritatta   \n",
       "14                           Spicy Flank Steak   \n",
       "15                                 Chicago Dip   \n",
       "\n",
       "                                                    1  \\\n",
       "_rowName                                                \n",
       "0                                   Chap Chee Noodles   \n",
       "1                            Old School Mac n' Cheese   \n",
       "2                                  Teriyaki Mushrooms   \n",
       "3                                    Cranberry Upside   \n",
       "4                                          Tasty Buns   \n",
       "5                Blueberry Cream Cheese Pound Cake II   \n",
       "6                              Coffee Butter Frosting   \n",
       "7                                           Baked Ham   \n",
       "8         Fried Cabbage with Bacon, Onion, and Garlic   \n",
       "9                                 Herbie's Home Fries   \n",
       "10                                   Carnitas Filling   \n",
       "11                Spinach, Red Lentil, and Bean Curry   \n",
       "12                         Slow Cooker BBQ Pork Chops   \n",
       "13              Filet Mignons With Pepper Cream Sauce   \n",
       "14                          Blissful Rosemary Chicken   \n",
       "15                                  Spaghetti Salad I   \n",
       "\n",
       "                                                          2  \n",
       "_rowName                                                     \n",
       "0         Sesame Crusted Mahi Mahi with Soy Shiso Ginger...  \n",
       "1                                  Superb Sauteed Mushrooms  \n",
       "2                                Country Scalloped Potatoes  \n",
       "3                                    Autumn Harvest Cookies  \n",
       "4                                    Mama D's Italian Bread  \n",
       "5                                 Hawaiian Banana Nut Bread  \n",
       "6                            Strawberry Cake and Frosting I  \n",
       "7                               Incredible Potato Casserole  \n",
       "8                                       Sesame Noodle Salad  \n",
       "9                                         Chocolate Wontons  \n",
       "10        Daddy's 'If They'da had This at the Alamo we w...  \n",
       "11                                      Lentils And Spinach  \n",
       "12                                   Slow Cooker Pork Chops  \n",
       "13                                      Hidden Cheeseburger  \n",
       "14                                             Chicken Melt  \n",
       "15                                 Cheesy  Potato Casserole  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.put(\"/v1/functions/nearestRecipe\", {\n",
    "    \"type\":\"embedding.neighbors\",\n",
    "    \"params\": { \"dataset\": \"rcp_svd_embedding\", \"defaultNumNeighbors\": 3 }\n",
    "})\n",
    "\n",
    "mldb.query(\"\"\"\n",
    "\n",
    "select nearestRecipe({coords: {*}})[neighbors] as * from rcp_kmeans_centroids\n",
    "\n",
    "\"\"\").applymap(lambda x: x.split('-')[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see a bit of pattern just from the names of the recipes nearest to the centroids, but we can probably do better! Let's try to extract the most characteristic words used in the recipe names for each cluster."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Topic Extraction with TF-IDF\n",
    "\n",
    "We'll start by preprocessing the recipe names a bit: taking out a few punctuations and convert to lowercase. And then for a given cluster, we will count the words taken from the recipe names. This is all done in one query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>absolutely</th>\n",
       "      <th>acorn</th>\n",
       "      <th>adobo</th>\n",
       "      <th>adrienne</th>\n",
       "      <th>african</th>\n",
       "      <th>aguadito</th>\n",
       "      <th>alaskan</th>\n",
       "      <th>alaska</th>\n",
       "      <th>alfredo</th>\n",
       "      <th>alla</th>\n",
       "      <th>...</th>\n",
       "      <th>vicious</th>\n",
       "      <th>vienna</th>\n",
       "      <th>wassail</th>\n",
       "      <th>weeknight</th>\n",
       "      <th>whit</th>\n",
       "      <th>willyboy</th>\n",
       "      <th>winner</th>\n",
       "      <th>wrapper</th>\n",
       "      <th>yankee</th>\n",
       "      <th>yumazuti</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16 rows × 3091 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          absolutely  acorn  adobo  adrienne  african  aguadito  alaskan  \\\n",
       "_rowName                                                                   \n",
       "0                  3      1      2         1        1         1        1   \n",
       "1                  1    NaN    NaN       NaN      NaN       NaN      NaN   \n",
       "2                  1    NaN    NaN       NaN      NaN       NaN      NaN   \n",
       "3                NaN    NaN    NaN       NaN      NaN       NaN      NaN   \n",
       "4                NaN    NaN    NaN       NaN      NaN       NaN      NaN   \n",
       "5                  2    NaN    NaN       NaN      NaN       NaN      NaN   \n",
       "6                NaN    NaN    NaN       NaN      NaN       NaN      NaN   \n",
       "7                NaN      1    NaN       NaN      NaN       NaN      NaN   \n",
       "8                  1      1    NaN       NaN      NaN       NaN      NaN   \n",
       "9                  3    NaN      1       NaN      NaN       NaN      NaN   \n",
       "10                 1    NaN    NaN       NaN      NaN       NaN      NaN   \n",
       "11               NaN      5    NaN       NaN        3       NaN      NaN   \n",
       "12               NaN    NaN      1       NaN      NaN       NaN      NaN   \n",
       "13               NaN      1      1       NaN        1       NaN      NaN   \n",
       "14               NaN    NaN      1       NaN      NaN       NaN        1   \n",
       "15                 1    NaN    NaN         1      NaN       NaN      NaN   \n",
       "\n",
       "          alaska  alfredo  alla    ...     vicious  vienna  wassail  \\\n",
       "_rowName                           ...                                \n",
       "0              1        1     1    ...         NaN     NaN      NaN   \n",
       "1            NaN        2     1    ...         NaN     NaN      NaN   \n",
       "2              1        2     1    ...         NaN     NaN      NaN   \n",
       "3            NaN      NaN   NaN    ...         NaN     NaN      NaN   \n",
       "4            NaN      NaN   NaN    ...         NaN     NaN      NaN   \n",
       "5            NaN      NaN   NaN    ...         NaN     NaN      NaN   \n",
       "6            NaN        1   NaN    ...         NaN     NaN      NaN   \n",
       "7            NaN        8   NaN    ...         NaN     NaN      NaN   \n",
       "8            NaN        2   NaN    ...         NaN     NaN      NaN   \n",
       "9            NaN        2   NaN    ...         NaN     NaN      NaN   \n",
       "10           NaN      NaN   NaN    ...         NaN     NaN      NaN   \n",
       "11           NaN        1   NaN    ...         NaN     NaN      NaN   \n",
       "12           NaN        2   NaN    ...         NaN     NaN      NaN   \n",
       "13           NaN        5     1    ...         NaN     NaN      NaN   \n",
       "14           NaN        2     2    ...         NaN     NaN      NaN   \n",
       "15           NaN        8   NaN    ...           1       1        1   \n",
       "\n",
       "          weeknight  whit  willyboy  winner  wrapper  yankee  yumazuti  \n",
       "_rowName                                                                \n",
       "0               NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "1               NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "2               NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "3               NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "4               NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "5               NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "6               NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "7               NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "8               NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "9               NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "10              NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "11              NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "12              NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "13              NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "14              NaN   NaN       NaN     NaN      NaN     NaN       NaN  \n",
       "15                1     1         1       1        1       1         1  \n",
       "\n",
       "[16 rows x 3091 columns]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print mldb.put('/v1/procedures/sum_words_per_cluster', {\n",
    "    'type': 'transform',\n",
    "    'params': {\n",
    "        'inputData': \"\"\"\n",
    "            select sum({tokens.* as *}) as * \n",
    "            named c.cluster\n",
    "            from (\n",
    "                SELECT lower(n.name),\n",
    "                    tokenize('recipe ' + lower(n.name), {splitChars:' -.;&!''()\",', minTokenLength: 4}) as tokens,\n",
    "                    c.cluster\n",
    "                FROM rcp_names as n \n",
    "                    JOIN rcp_kmeans_clusters as c ON (n.rowName() = c.rowPathElement(0))\n",
    "                order by n.rowName()\n",
    "            )\n",
    "            group by c.cluster\n",
    "        \"\"\",\n",
    "        'outputDataset': 'rcp_cluster_word_counts',\n",
    "        'runOnCreation': True\n",
    "    }\n",
    "})\n",
    "\n",
    "mldb.query(\"\"\"select * from rcp_cluster_word_counts order by implicit_cast(rowName())\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use this to create a [TF-IDF score](https://en.wikipedia.org/wiki/Tf%E2%80%93idf) for each word in the cluster. Basically this score will give us an idea of the relative importance of a each word in a given cluster."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n",
      "<Response [201]>\n",
      "<Response [201]>\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>absolutely</th>\n",
       "      <th>acorn</th>\n",
       "      <th>adobo</th>\n",
       "      <th>adrienne</th>\n",
       "      <th>african</th>\n",
       "      <th>aguadito</th>\n",
       "      <th>alaskan</th>\n",
       "      <th>alaska</th>\n",
       "      <th>alfredo</th>\n",
       "      <th>alla</th>\n",
       "      <th>...</th>\n",
       "      <th>vicious</th>\n",
       "      <th>vienna</th>\n",
       "      <th>wassail</th>\n",
       "      <th>weeknight</th>\n",
       "      <th>whit</th>\n",
       "      <th>willyboy</th>\n",
       "      <th>winner</th>\n",
       "      <th>wrapper</th>\n",
       "      <th>yankee</th>\n",
       "      <th>yumazuti</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.797624</td>\n",
       "      <td>0.679859</td>\n",
       "      <td>1.077551</td>\n",
       "      <td>1.160312</td>\n",
       "      <td>0.960906</td>\n",
       "      <td>1.441359</td>\n",
       "      <td>1.160312</td>\n",
       "      <td>1.160312</td>\n",
       "      <td>0.143925</td>\n",
       "      <td>0.679859</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.398812</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.228115</td>\n",
       "      <td>0.679859</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.398812</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.160312</td>\n",
       "      <td>0.228115</td>\n",
       "      <td>0.679859</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.632102</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.143925</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.679859</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.456230</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.398812</td>\n",
       "      <td>0.679859</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.228115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.797624</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.679859</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.228115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>0.398812</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1.757410</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.921812</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.143925</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.679859</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.228115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.679859</td>\n",
       "      <td>0.679859</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.960906</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.372040</td>\n",
       "      <td>0.679859</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.679859</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.160312</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.228115</td>\n",
       "      <td>1.077551</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>0.398812</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.160312</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.456230</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1.441359</td>\n",
       "      <td>1.441359</td>\n",
       "      <td>1.441359</td>\n",
       "      <td>1.441359</td>\n",
       "      <td>1.441359</td>\n",
       "      <td>1.441359</td>\n",
       "      <td>1.441359</td>\n",
       "      <td>1.441359</td>\n",
       "      <td>1.441359</td>\n",
       "      <td>1.441359</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>16 rows × 3091 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          absolutely     acorn     adobo  adrienne   african  aguadito  \\\n",
       "_rowName                                                                 \n",
       "0           0.797624  0.679859  1.077551  1.160312  0.960906  1.441359   \n",
       "1           0.398812       NaN       NaN       NaN       NaN       NaN   \n",
       "2           0.398812       NaN       NaN       NaN       NaN       NaN   \n",
       "3                NaN       NaN       NaN       NaN       NaN       NaN   \n",
       "4                NaN       NaN       NaN       NaN       NaN       NaN   \n",
       "5           0.632102       NaN       NaN       NaN       NaN       NaN   \n",
       "6                NaN       NaN       NaN       NaN       NaN       NaN   \n",
       "7                NaN  0.679859       NaN       NaN       NaN       NaN   \n",
       "8           0.398812  0.679859       NaN       NaN       NaN       NaN   \n",
       "9           0.797624       NaN  0.679859       NaN       NaN       NaN   \n",
       "10          0.398812       NaN       NaN       NaN       NaN       NaN   \n",
       "11               NaN  1.757410       NaN       NaN  1.921812       NaN   \n",
       "12               NaN       NaN  0.679859       NaN       NaN       NaN   \n",
       "13               NaN  0.679859  0.679859       NaN  0.960906       NaN   \n",
       "14               NaN       NaN  0.679859       NaN       NaN       NaN   \n",
       "15          0.398812       NaN       NaN  1.160312       NaN       NaN   \n",
       "\n",
       "           alaskan    alaska   alfredo      alla    ...      vicious  \\\n",
       "_rowName                                            ...                \n",
       "0         1.160312  1.160312  0.143925  0.679859    ...          NaN   \n",
       "1              NaN       NaN  0.228115  0.679859    ...          NaN   \n",
       "2              NaN  1.160312  0.228115  0.679859    ...          NaN   \n",
       "3              NaN       NaN       NaN       NaN    ...          NaN   \n",
       "4              NaN       NaN       NaN       NaN    ...          NaN   \n",
       "5              NaN       NaN       NaN       NaN    ...          NaN   \n",
       "6              NaN       NaN  0.143925       NaN    ...          NaN   \n",
       "7              NaN       NaN  0.456230       NaN    ...          NaN   \n",
       "8              NaN       NaN  0.228115       NaN    ...          NaN   \n",
       "9              NaN       NaN  0.228115       NaN    ...          NaN   \n",
       "10             NaN       NaN       NaN       NaN    ...          NaN   \n",
       "11             NaN       NaN  0.143925       NaN    ...          NaN   \n",
       "12             NaN       NaN  0.228115       NaN    ...          NaN   \n",
       "13             NaN       NaN  0.372040  0.679859    ...          NaN   \n",
       "14        1.160312       NaN  0.228115  1.077551    ...          NaN   \n",
       "15             NaN       NaN  0.456230       NaN    ...     1.441359   \n",
       "\n",
       "            vienna   wassail  weeknight      whit  willyboy    winner  \\\n",
       "_rowName                                                                \n",
       "0              NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "1              NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "2              NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "3              NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "4              NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "5              NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "6              NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "7              NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "8              NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "9              NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "10             NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "11             NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "12             NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "13             NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "14             NaN       NaN        NaN       NaN       NaN       NaN   \n",
       "15        1.441359  1.441359   1.441359  1.441359  1.441359  1.441359   \n",
       "\n",
       "           wrapper    yankee  yumazuti  \n",
       "_rowName                                \n",
       "0              NaN       NaN       NaN  \n",
       "1              NaN       NaN       NaN  \n",
       "2              NaN       NaN       NaN  \n",
       "3              NaN       NaN       NaN  \n",
       "4              NaN       NaN       NaN  \n",
       "5              NaN       NaN       NaN  \n",
       "6              NaN       NaN       NaN  \n",
       "7              NaN       NaN       NaN  \n",
       "8              NaN       NaN       NaN  \n",
       "9              NaN       NaN       NaN  \n",
       "10             NaN       NaN       NaN  \n",
       "11             NaN       NaN       NaN  \n",
       "12             NaN       NaN       NaN  \n",
       "13             NaN       NaN       NaN  \n",
       "14             NaN       NaN       NaN  \n",
       "15        1.441359  1.441359  1.441359  \n",
       "\n",
       "[16 rows x 3091 columns]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print mldb.put('/v1/procedures/train_tfidf', {\n",
    "     'type': 'tfidf.train',\n",
    "     'params': {\n",
    "         'trainingData': \"select * from rcp_cluster_word_counts\",\n",
    "         'modelFileUrl': 'file:///mldb_data/models/rcp_tfidf.idf',\n",
    "         'runOnCreation': True\n",
    "    }\n",
    "})\n",
    "\n",
    "print mldb.put('/v1/functions/rcp_tfidf', {\n",
    "     'type': 'tfidf',\n",
    "     'params': {\n",
    "         'modelFileUrl': 'file:///mldb_data/models/rcp_tfidf.idf',\n",
    "         'tfType': 'log', 'idfType': 'inverse'\n",
    "    }\n",
    "})\n",
    "\n",
    "\n",
    "print mldb.put('/v1/procedures/apply_tfidf', {\n",
    "     'type': 'transform',\n",
    "     'params': {\n",
    "         'inputData': \"select rcp_tfidf({input: {*}})[output] as * from rcp_cluster_word_counts\",\n",
    "         'outputDataset': 'rcp_cluster_word_scores',\n",
    "         'runOnCreation': True\n",
    "    }\n",
    "})\n",
    "\n",
    "mldb.query(\"select * from rcp_cluster_word_scores order by implicit_cast(rowName())\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we transpose that dataset, we will be able to get the highest scored words for each cluster, and we can display them nicely in a word cloud."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"850\"\n",
       "            height=\"350\"\n",
       "            src=\"data:text/html,\n",
       "<script src='https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.6/d3.min.js'></script>\n",
       "<script src='https://static.mldb.ai/d3.layout.cloud.js'></script>\n",
       "<script src='https://static.mldb.ai/wordcloud.js'></script>\n",
       "<body> <script>drawCloud([{'text': 'tater', 'size': 20}, {'text': 'hashbrown', 'size': 19}, {'text': 'deviled', 'size': 18}, {'text': 'incredibly', 'size': 17}, {'text': 'celery', 'size': 16}, {'text': 'cube', 'size': 15}, {'text': 'twice', 'size': 14}, {'text': 'incredible', 'size': 13}, {'text': 'poor', 'size': 12}, {'text': 'sunshine', 'size': 11}, {'text': 'round', 'size': 10}, {'text': 'most', 'size': 9}, {'text': 'extra', 'size': 8}, {'text': 'viii', 'size': 7}, {'text': 'rings', 'size': 6}, {'text': 'homestyle', 'size': 5}, {'text': 'campfire', 'size': 4}, {'text': 'hamburger', 'size': 3}, {'text': 'chipulos', 'size': 2}, {'text': 'alysia', 'size': 1}])</script> </body>\n",
       "\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x7f9efafe1dd0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import json\n",
    "from ipywidgets import interact \n",
    "from IPython.display import IFrame, display\n",
    "html = \"\"\"\n",
    "<script src=\"https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.6/d3.min.js\"></script>\n",
    "<script src=\"https://static.mldb.ai/d3.layout.cloud.js\"></script>\n",
    "<script src=\"https://static.mldb.ai/wordcloud.js\"></script>\n",
    "<body> <script>drawCloud(%s)</script> </body>\n",
    "\"\"\"\n",
    "\n",
    "@interact \n",
    "def cluster_word_cloud(cluster=[0, num_centroids-1]):\n",
    "    num_words = 20\n",
    "    cluster_words = mldb.get(\n",
    "        '/v1/query',\n",
    "        q=\"\"\"\n",
    "            SELECT rowName() as text\n",
    "            FROM transpose(rcp_cluster_word_scores)\n",
    "            ORDER BY \"{0}\" DESC\n",
    "            LIMIT {1}\n",
    "          \"\"\".format(cluster, num_words),\n",
    "        format='aos',\n",
    "        rowNames=0\n",
    "    ).json()\n",
    "    for i,x in enumerate(cluster_words):\n",
    "        x['size'] = num_words - i\n",
    "    display( IFrame(\"data:text/html,\" + (html % json.dumps(cluster_words)).replace('\"',\"'\"), 850, 350) )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Much better!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Where to next?\n",
    "\n",
    "Check out the other [Tutorials and Demos](../../../../doc/#builtin/Demos.md.html)."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
